using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;


public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void AuthorizeUser(SqlString Username, SqlString Password, SqlGuid ApplicationGuid)
    {
        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            try
            {
                connection.Open();

                string findUserCommandText = "SELECT COUNT(UserGuid) FROM Security_User WHERE LOWER(UserName) = LOWER(@Username) AND Active = 1";

                SqlCommand findUserCommand = new SqlCommand(findUserCommandText, connection);

                findUserCommand.Parameters.Add(new SqlParameter("@Username", Username));

                int userCount = (int)findUserCommand.ExecuteScalar();

                Guid sessionGuid = Guid.Empty;

                if (userCount == 1)
                {
                    string authorizeCommandText = "SELECT COUNT(UserGuid) FROM Security_User WHERE LOWER(Username) = LOWER(@Username) AND Password = @Password";

                    SqlCommand authorizeCommand = new SqlCommand(authorizeCommandText, connection);

                    authorizeCommand.Parameters.Add(new SqlParameter("@Username", Username));
                    authorizeCommand.Parameters.Add(new SqlParameter("@Password", Password));

                    int authorizedUserCount = (int)authorizeCommand.ExecuteScalar();

                    if (authorizedUserCount == 1)
                    {
                        authorizeCommandText = "SELECT UserGuid FROM Security_User WHERE LOWER(Username) = LOWER(@Username) AND Password = @Password";

                        authorizeCommand = new SqlCommand(authorizeCommandText, connection);

                        authorizeCommand.Parameters.Add(new SqlParameter("@Username", Username));
                        authorizeCommand.Parameters.Add(new SqlParameter("@Password", Password));

                        Guid authorizedUserGuid = (Guid)authorizeCommand.ExecuteScalar();

                        sessionGuid = Guid.NewGuid();

                        string newSessionCommandText = "";

                        newSessionCommandText = "INSERT INTO Security_Session (SessionGuid, UserGuid, ApplicationGuid) VALUES (@SessionGuid, @UserGuid, @ApplicationGuid)";

                        System.Data.SqlClient.SqlCommand newSessionCommand = new System.Data.SqlClient.SqlCommand(newSessionCommandText, connection);

                        newSessionCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@SessionGuid", sessionGuid));
                        newSessionCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@UserGuid", authorizedUserGuid));
                        newSessionCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ApplicationGuid", ApplicationGuid));

                        newSessionCommand.ExecuteNonQuery();
                    }
                    else if (authorizedUserCount == 0)
                    {
                        // password is incorrect
                    }
                }
                else if (userCount == 0)
                {
                    // user does not exist
                }

                string resultCommandText = "SELECT @SessionGuid AS SessionGuid";

                SqlCommand resultCommand = new SqlCommand(resultCommandText, connection);

                resultCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@SessionGuid", sessionGuid));

                SqlContext.Pipe.ExecuteAndSend(resultCommand);
            }
            catch (System.Exception exception)
            {
                SqlContext.Pipe.Send("An exception occured");
                SqlContext.Pipe.Send("Message: " + exception.Message);
                SqlContext.Pipe.Send("Source: " + exception.Source);
                SqlContext.Pipe.Send("Stack: " + exception.StackTrace);
                SqlContext.Pipe.Send("Target: " + exception.TargetSite);
            }
            finally
            {
                connection.Close();
            }
        }
    }
};
